#!/usr/bin/python
#coding=utf-8

from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
# from common import df_operator as op, db_utils as util
from common import db_utils as util
import sys


dtgC45s = """
    CREATE TABLE BGTARAS1.PREQ412_DTG_C45S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        HUMI1_DEW_PV DECIMAL(24,6),
        HUMI2_DEW_PV DECIMAL(24,6),
        HUMI3_DEW_PV DECIMAL(24,6),
        HUMI4_DEW_PV DECIMAL(24,6),
        MIX1_H2_PV DECIMAL(24,6),
        MIX2_H2_PV DECIMAL(24,6),
        MIX3_H2_PV DECIMAL(24,6),
        MIX4_H2_PV DECIMAL(24,6),
        MIX5_H2_PV DECIMAL(24,6),
        MIX6_H2_PV DECIMAL(24,6),
        MIX7_H2_PV DECIMAL(24,6),
        ENTRY_N2_FLOW DECIMAL(24,6),
        MIX1_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX2_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX3_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX4_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX5_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX6_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX7_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX1_N2H2_FLOW_PV DECIMAL(24,6),
        MIX2_N2H2_FLOW_PV DECIMAL(24,6),
        MIX3_N2H2_FLOW_PV DECIMAL(24,6),
        MIX4_N2H2_FLOW_PV DECIMAL(24,6),
        MIX5_N2H2_FLOW_PV DECIMAL(24,6),
        MIX6_N2H2_FLOW_PV DECIMAL(24,6),
        MIX7_N2H2_FLOW_PV DECIMAL(24,6),
        H2_RECYC_TFLOW DECIMAL(24,6),
        RTF1_Z1_FTEMP_PV DECIMAL(24,6),
        RTF1_Z2_FTEMP_PV DECIMAL(24,6),
        RTF1_Z3_FTEMP_PV DECIMAL(24,6),
        RTF2_Z1_FTEMP_PV DECIMAL(24,6),
        RTF2_Z2_FTEMP_PV DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""



def createDTG_C45S(start_time,end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ412_DTG_C45S' AND TABSCHEMA='BGTARAS1'"""
    sql_result = util.query(conn_rds, sql=sql)
    if sql_result.__len__() > 0 :
        conn_rds.execute("drop table BGTARAS1.PREQ412_DTG_C45S")
    else:
        conn_rds.execute(dtgC45s)

    sql = """WITH
            TMP AS
            (
                SELECT
                    A.UNIT_CODE,
                    A.CLASS,
                    A.SHIFT,
                    A.PRODUCE_TIME,
                    A.PROD_COILNO,
                    A.ENTRY_COILNO,
                    CONCAT(B.START_TIME_DATE, B.START_TIME_TIME) AS START_TIME,
                    CONCAT(B.END_TIME_DATE, B.END_TIME_TIME)     AS END_TIME
                FROM
                    BGTARAS1.T_ADS_FACT_SICB_CD0201 a,
                    BGTAROQ412.DTG_DCDT B
                WHERE
                    A.UNIT_CODE='Q412'
                AND A.COST_CENTER='MIHB'
                AND B.PRD_COILNO=A.PROD_COILNO
                AND a.PRODUCE_TIME BETWEEN """+start_time+""" AND """+end_time+"""
            )
        SELECT
            A.UNIT_CODE,
            A.CLASS,
            A.SHIFT,
            A.PRODUCE_TIME,
            A.PROD_COILNO,
            A.ENTRY_COILNO,
            AVG(CAST(C.HUMI1_DEW_SV AS DOUBLE)/10)      AS HUMI1_DEW_PV,--加湿器露点设定
            AVG(CAST(C.HUMI2_DEW_SV AS DOUBLE)/10)      AS HUMI2_DEW_PV,
            AVG(CAST(C.HUMI3_DEW_SV AS DOUBLE)/10)      AS HUMI3_DEW_PV,
            AVG(CAST(C.HUMI4_DEW_SV AS DOUBLE)/10)      AS HUMI4_DEW_PV,
            AVG(CAST(C.MIX1_H2_SV AS DOUBLE)/10)        AS MIX1_H2_PV,--混气站氢气浓度
            AVG(CAST(C.MIX2_H2_SV AS DOUBLE)/10)        AS MIX2_H2_PV,
            AVG(CAST(C.MIX3_H2_SV AS DOUBLE)/10)        AS MIX3_H2_PV,
            AVG(CAST(C.MIX4_H2_SV AS DOUBLE)/10)        AS MIX4_H2_PV,
            AVG(CAST(C.MIX5_H2_SV AS DOUBLE)/10)        AS MIX5_H2_PV,
            AVG(CAST(C.MIX6_H2_SV AS DOUBLE)/10)        AS MIX6_H2_PV,
            AVG(CAST(C.MIX7_H2_SV AS DOUBLE)/10)        AS MIX7_H2_PV, --入口密封氮气流量
            AVG(CAST(C.ENTRY_N2_FLOW AS DOUBLE)/10)     AS ENTRY_N2_FLOW,
            AVG(C.MIX1_N2H2_FLOW_PV*10*(1-CAST(C.MIX1_H2_PV AS DOUBLE)/100)/10) AS MIX1_N2_PIPE_FLOW_PV_AVG,-- 混气站氮气流量
            AVG(C.MIX2_N2H2_FLOW_PV*10*(1-CAST(C.MIX2_H2_PV AS DOUBLE)/100)/10) AS MIX2_N2_PIPE_FLOW_PV_AVG,
            AVG(C.MIX3_N2H2_FLOW_PV*10*(1-CAST(C.MIX3_H2_PV AS DOUBLE)/100)/10) AS MIX3_N2_PIPE_FLOW_PV_AVG,
            AVG(C.MIX4_N2H2_FLOW_PV*10*(1-CAST(C.MIX4_H2_PV AS DOUBLE)/100)/10) AS MIX4_N2_PIPE_FLOW_PV_AVG,
            AVG(C.MIX5_N2H2_FLOW_PV*10*(1-CAST(C.MIX5_H2_PV AS DOUBLE)/100)/10) AS MIX5_N2_PIPE_FLOW_PV_AVG,
            AVG(C.MIX6_N2H2_FLOW_PV*10*(1-CAST(C.MIX6_H2_PV AS DOUBLE)/100)/10) AS MIX6_N2_PIPE_FLOW_PV_AVG,
            AVG(C.MIX7_N2H2_FLOW_PV*10*(1-CAST(C.MIX7_H2_PV AS DOUBLE)/100)/10) AS MIX7_N2_PIPE_FLOW_PV_AVG,
            AVG(CAST(C.MIX1_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX1_N2H2_FLOW_PV,--混气N2H2总流量
            AVG(CAST(C.MIX2_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX2_N2H2_FLOW_PV,
            AVG(CAST(C.MIX3_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX3_N2H2_FLOW_PV,
            AVG(CAST(C.MIX4_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX4_N2H2_FLOW_PV,
            AVG(CAST(C.MIX5_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX5_N2H2_FLOW_PV,
            AVG(CAST(C.MIX6_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX6_N2H2_FLOW_PV,
            AVG(CAST(C.MIX7_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX7_N2H2_FLOW_PV,--氢气回收管道流量
            AVG(CAST(C.H2_RECYC_TFLOW AS DOUBLE)/10)    AS H2_RECYC_TFLOW,
            AVG(CAST(D.RTF1_Z1_FTEMP_PV AS DOUBLE)/10) AS RTF1_Z1_FTEMP_PV,
            AVG(CAST(D.RTF1_Z2_FTEMP_PV AS DOUBLE)/10) AS RTF1_Z2_FTEMP_PV,
            AVG(CAST(D.RTF1_Z3_FTEMP_PV AS DOUBLE)/10) AS RTF1_Z3_FTEMP_PV,
            AVG(CAST(D.RTF2_Z1_FTEMP_PV AS DOUBLE)/10) AS RTF2_Z1_FTEMP_PV,
            AVG(CAST(D.RTF2_Z2_FTEMP_PV AS DOUBLE)/10) AS RTF2_Z2_FTEMP_PV
        FROM
            TMP A
        LEFT JOIN
            BGTAROQ412.DTG_C45S c
        ON
            C.UP_DATE BETWEEN A.START_TIME AND A.END_TIME
        LEFT JOIN
            BGTAROQ412.DTG_C25S D
        ON
            D.UP_DATE BETWEEN A.START_TIME AND A.END_TIME
        GROUP BY
            A.UNIT_CODE,
            A.CLASS,
            A.SHIFT,
            A.PRODUCE_TIME,
            A.PROD_COILNO,
            A.ENTRY_COILNO
        """
    df_result = util.query(conn_rds, sql=sql)
    util.insert(df_result, conn_rds, "PREQ412_DTG_C45S", "BGTARAS1")


def PreQ412(start_time, end_time):
    global conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    createDTG_C45S(start_time, end_time)
    conn_rds.close()

def main():
    PreQ412('20211014200514','20211014200514')

if __name__ == '__main__':
    main()